package com.eascs.loan.controller;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.Format;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.inject.Inject;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.dubbo.config.annotation.Reference;
import com.eascs.admin.common.service.ILoanInfoService;
import com.eascs.admin.common.utils.StringUtils;
import com.eascs.admin.common.vo.O2oUpdateLoanExpireTimeEntity;
import com.eascs.admin.o2o.internal.util.StatusContexts;
import com.eascs.admin.o2o.loan.entity.O2oLoanapplyEntity;
import com.eascs.admin.o2o.loan.service.O2oLoanapplyService;
import com.eascs.app.dao.PageResult;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.doc.excel.ExcelApiHelper;
import com.eascs.common.doc.excel.RowReadHandler;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.common.util.UrlUtil;
import com.eascs.crm.common.entity.CustomerBasicInfoEntity;
import com.eascs.crm.common.service.ICustomerInfoService;
import com.eascs.crm.common.vo.QueryCustomerVO;
import com.eascs.loan.entity.O2oBankLoanData;
import com.eascs.loan.entity.O2oPaymentsPlan;
import com.eascs.loan.form.O2oLoanForm;
import com.eascs.loan.form.O2oPaymentsForm;
import com.eascs.loan.service.O2oLoanService;
import com.eascs.loan.service.O2oPaymentsService;
import com.eascs.loan.service.impl.O2oLoanServiceImpl;
import com.eascs.loan.service.impl.O2oPaymentsServiceImpl;
import com.eascs.site.controller.BaseRestController;
import com.eascs.site.page.PaginationInfo;
import com.eascs.site.page.SelectDataOption;
import com.eascs.site.view.BaseRestResult;
import com.eascs.site.view.HtmlRestResult;
import com.eascs.site.view.PaginationRestResult;
import com.eascs.site.view.Render;
import com.eascs.site.view.ViewContext;
import com.eascs.site.view.button.AjaxButton;
import com.eascs.site.view.button.AjaxConfirmButton;
import com.eascs.site.view.button.Button;
import com.eascs.site.view.button.ButtonHelper;
import com.eascs.site.view.button.ShowButtonFilter;
import com.eascs.web.context.RequestContext;
import com.eascs.web.sso.User;
import com.eascs.web.sso.UserHolder;
import com.eascs.workflow.common.service.sys.SysOrgService;
import com.eascs.workflow.service.WorkFlowSumbitService;

/**
 * @ClassName: O2oLoanRestController
 * @author user
 * @date 2016年7月15日 上午11:10:16
 * @version V1.0
 */
@RestController
@RequestMapping("/bank")
public class O2oLoanRestController extends BaseRestController {
	private static Logger log = LoggerFactory.getLogger(O2oLoanRestController.class);
	@Inject
	O2oLoanService o2oLoanService;

	@Autowired
	private O2oLoanapplyService loanService;
	@Inject
	O2oPaymentsService o2oPaymentsService;
	@Reference
	private ICustomerInfoService customerInfoService;
	@Reference
	ILoanInfoService iLoanInfoService;
	@Autowired
	O2oPaymentsServiceImpl paymentServiceImpl;
	@Autowired
	private O2oLoanServiceImpl o2oLoanServiceImpl;
	
	@Reference
	SysOrgService sysOrgService;
	@Autowired
	private WorkFlowSumbitService workFlowSumbitService;
	@RequestMapping("/save_loan")
	public BaseRestResult<?> saveLoan(O2oLoanForm o2oLoanForm) {
		Map<String, String> map = workFlowSumbitService.saveLoan(o2oLoanForm);
		String flag = map.get("flag");
		BaseRestResult<?> result = null;
		if ("success".equals(flag)) {
			O2oLoanapplyEntity loan = loanService.fingByBillCode(o2oLoanForm.getBillCode());
			O2oUpdateLoanExpireTimeEntity updateLoanTime = new O2oUpdateLoanExpireTimeEntity();
			updateLoanTime.setLoanapplyNo(loan.getLoanapplyNo());
			updateLoanTime.setLoanTime(DateUtils.formateStandDate(o2oLoanForm.getLoanDate()));
			updateLoanTime.setTerm(o2oLoanForm.getLoanTerm());
			updateLoanTime.setExpireTime();
			iLoanInfoService.updateLoanExpireTime(updateLoanTime);
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			if(o2oLoanForm.getLoanDate()!=null){
				try {
					loan.setPaymentTime(sdf.parse(o2oLoanForm.getLoanDate()));
					loanService.updateEntityById(loan);
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			result = BaseRestResult.info("保存成功").andRedirect("/bank/loan_list?billCode=" + map.get("billCode"));
		} else if ("fail".equals(flag)) {
			result = BaseRestResult.error("保存失败");
		}
		return result;
	}

	/**
	 * @Title: addLoan
	 * @Description: 增加银行放款数据
	 * @author user
	 * @param o2oLoanForm
	 * @return
	 * @return String
	 * @throws @date
	 *             2016年7月15日 上午10:22:03
	 */
	@RequestMapping("/add_loan_div")
	public HtmlRestResult addLoanDiv(O2oLoanForm o2oLoanForm) {
		if (StringUtils.isNotEmpty(o2oLoanForm.getBillCode())) {
			List<Object[]> bill_list = o2oLoanService.isBillCodeExists(o2oLoanForm.getBillCode(), null, null);
			if ((bill_list != null && bill_list.size() > 0)) {
				Object[] o = bill_list.get(0);
				if (o[1] != null) {
					String loanerCard = (String) o[1];
					o2oLoanForm.setLoanerCard(loanerCard);
				}
				if (o[2] != null) {
					String loanerName = (String) o[2];
					o2oLoanForm.setLoanerName(loanerName);
				}
				if (o[3] != null) {
					String loanBank = (String) o[3];
					o2oLoanForm.setLoanBank(loanBank);
				}
				if (o[4] != null) {
					BigDecimal applyAmount = (BigDecimal) o[4];
					o2oLoanForm.setApplyAmount(applyAmount);
				}
				if (o[5] != null) {
					BigDecimal loanRate = (BigDecimal) o[5];
					o2oLoanForm.setLoanRate(loanRate);
				}

				if (o[6] != null) {
					BigDecimal loanAmount = (BigDecimal) o[6];
					o2oLoanForm.setLoanAmount(loanAmount);
				}
				if (o[7] != null) {
					int loanTerm = (int) o[7];
					o2oLoanForm.setLoanTerm(loanTerm);
				}
				if (o[8] != null) {
					String loanDate = (String) o[8];
					o2oLoanForm.setLoanDate(loanDate);
				}
			}
		}
		ViewContext.put("voo", o2oLoanForm);
		// 银行还款计划列表
		payMents(o2oLoanForm.getBillCode());
		return HtmlRestResult.popHtmlFragment("/bank/add_loan_div");
	}

	public void payMents(String billCode) {
		O2oPaymentsForm o2oPaymentsForm = new O2oPaymentsForm();
		o2oPaymentsForm.setBillCode(billCode);
		o2oPaymentsForm.setPageSize(100);
		PageResult<Object[]> pageResult = o2oPaymentsService.paymentsList(o2oPaymentsForm);
		String[] titles = { "贷款单号", "借款人姓名", "借款人证件号", "还款期数", "应还款日期", "剩余本金", "应还本金", "应还利息", "本期应还款" };
		String pageUrl = "/payments/payments_list";
		PaginationInfo paginationInfo = new PaginationInfo(pageResult, titles, pageUrl + "&showPage=");
		paginationInfo.setBuildPagingNavigation(false);
		paginationInfo.setFirstRenderColumnIndex(1);
		AjaxConfirmButton[] dealButton = new AjaxConfirmButton[] { new AjaxConfirmButton("删除", "/paymentsRest/delPayments?payments_id=") };
		paginationInfo.setFormats(new Format[] { null, null, null, null, null, new DecimalFormat(",##0.00"), new DecimalFormat(",##0.00"), new DecimalFormat(",##0.00"), new DecimalFormat(",##0.00") });
		paginationInfo.setMoreButtons(dealButton);
		ViewContext.put("paginationInfo", paginationInfo);
		ViewContext.put("vo", o2oPaymentsForm);
		if (pageResult.getData().size() > 0) {
			ViewContext.put("upload", "upload");
		}
	}

	/**
	 * @Title: importExcel
	 * @Description: 还款计划数据导入
	 * @author zqq
	 * @param file
	 * @param billCode
	 * @return void
	 * @throws @date
	 *             2016年7月15日 上午10:25:09
	 */
	@RequestMapping("/import")
	public BaseRestResult<Render[]> importExcel(@RequestParam(value = "file", required = false) MultipartFile file, String billCode, String loanerName, String idNum) {
		InputStream inputStream = null;

		try {
			inputStream = file.getInputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}

		try {
			ExcelApiHelper.readHandle(inputStream, new RowReadHandler() {

				@Override
				public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

					checkQtherBank(sheetIndex, rowIndex, rowDatas, 0, billCode, loanerName, idNum);
				}

			});

		} catch (RuntimeException e) {
			String message = e.getMessage();
			message = e.getMessage().substring(27, message.length());
			return BaseRestResult.render(new Render[] { new Render("#errorMessage1", message) });
			// ViewContext.put("errorMessage", message);

		}

		InputStream inputStream1 = null;

		try {
			inputStream1 = file.getInputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}

		try {
			ExcelApiHelper.readHandle(inputStream1, new RowReadHandler() {

				@Override
				public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

					checkQtherBank(sheetIndex, rowIndex, rowDatas, 1, billCode, loanerName, idNum);
				}

			});

		} catch (RuntimeException e) {
			String message = e.getMessage();
			message = e.getMessage().substring(27, message.length());
			// ViewContext.put("errorMessage", message);
			return BaseRestResult.render(new Render[] { new Render("#errorMessage1", message) });

		}

		return BaseRestResult.render(new Render[] { new Render("#errorMessage1", "导入成功"), new Render("#flag", "flag") });

	}

	private boolean isValidDate(String str) {
		// String str = "2007-01-02";
		if(!StringUtil.isEmpty(str)){
			str=str.replace("/", "-");
		}
		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		try {
			Date date = (Date) formatter.parse(str);
			return str.equals(formatter.format(date));
		} catch (Exception e) {
			return false;
		}
	}
	
	private boolean isValidDate1(String str) {
		// String str = "2007-01-02";
		
		DateFormat formatter = new SimpleDateFormat("yyyyMMdd");
		try {
			Date date = (Date) formatter.parse(str);
			return str.equals(formatter.format(date));
		} catch (Exception e) {
			return false;
		}
	}
	
	
	/**
	 * 银行放款数据导入还款计划 刷新
	 * 
	 * @param file
	 * @param billCode
	 * @param loanerName
	 * @param idNum
	 * @return
	 */
	@RequestMapping("/uploadBankExcel")
	public BaseRestResult<String> uploadBankExcel(@RequestParam(value = "file", required = false) MultipartFile file,
			String billCode, String loanerName, String idNum) {
		O2oBankLoanData bankLoan = o2oLoanServiceImpl.queryBillCode(billCode);
		if (bankLoan==null) {
			return BaseRestResult.info("提示：“请先保存银行放款数据，再导入还款计划。”");
		}
		String username = bankLoan.getLoanerName();
		InputStream inputStream = null;
		try {
			inputStream = file.getInputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			if (bankLoan != null && StringUtil.isNotBlank(bankLoan.getLoanBank())) {
				if (bankLoan.getLoanBank().equals("JCB")) {

					ExcelApiHelper.readHandle(inputStream, new RowReadHandler() {

						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkJCBank(sheetIndex, rowIndex, rowDatas, 0, billCode, loanerName, idNum);
						}

					});
				} else if (bankLoan.getLoanBank().equals("GACZ")||bankLoan.getLoanBank().equals("ZZB")) {
					HSSFWorkbook workbook = null;
					try {
						workbook = new HSSFWorkbook(inputStream);
					} catch (FileNotFoundException e) {
						
						throw new RuntimeException("没有找到该银行的还款计划的模板");
					} catch (IOException e) {
						throw new RuntimeException("没有找到该银行的还款计划的模板");
					}

					checkGACZBank(workbook,username, 0, billCode, loanerName, idNum);

				}else if (bankLoan.getLoanBank().equals("SPDBGZ")) {
					ExcelApiHelper.readHandle(inputStream, new RowReadHandler() {
						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkSPDBGZBank(sheetIndex, rowIndex, rowDatas, 0, billCode, loanerName, idNum);
						}
					});

				}else {

					ExcelApiHelper.readHandle(inputStream, new RowReadHandler() {
						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkQtherBank(sheetIndex, rowIndex, rowDatas, 0, billCode, loanerName, idNum);
						}
					});

				}

			}
		} catch (RuntimeException e) {
			String message = e.getMessage();
			if (bankLoan != null && StringUtil.isNotBlank(bankLoan.getLoanBank())) {
				if (!bankLoan.getLoanBank().equals("GACZ")&&!bankLoan.getLoanBank().equals("ZZB")) {
					message = e.getMessage().substring(27, message.length());
				}
			}
			log.info("导入Exception={}", message);			
			return BaseRestResult.warn(message);
		}
		InputStream inputStream1 = null;
		try {
			inputStream1 = file.getInputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			if (bankLoan != null && StringUtil.isNotBlank(bankLoan.getLoanBank())) {
				if (bankLoan.getLoanBank().equals("JCB")) {

					ExcelApiHelper.readHandle(inputStream1, new RowReadHandler() {

						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkJCBank(sheetIndex, rowIndex, rowDatas, 1, billCode, loanerName, idNum);
						}

					});
				} else if (bankLoan.getLoanBank().equals("GACZ")||bankLoan.getLoanBank().equals("ZZB")) {
					HSSFWorkbook workbook = null;
					try {
						workbook = new HSSFWorkbook(inputStream1);
					} catch (FileNotFoundException e) {
						
						throw new RuntimeException("没有找到国安村镇还款计划的模板");
					} catch (IOException e) {
						throw new RuntimeException("没有找到国安村镇还款计划的模板");
					}
					checkGACZBank(workbook,username, 1, billCode, loanerName, idNum);

				}else if (bankLoan.getLoanBank().equals("SPDBGZ")) {
					ExcelApiHelper.readHandle(inputStream1, new RowReadHandler() {
						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkSPDBGZBank(sheetIndex, rowIndex, rowDatas, 1, billCode, loanerName, idNum);
						}
					});

				} else {

					ExcelApiHelper.readHandle(inputStream1, new RowReadHandler() {
						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkQtherBank(sheetIndex, rowIndex, rowDatas, 1, billCode, loanerName, idNum);
						}
					});

				}
			}

		} catch (RuntimeException e) {
			String message = e.getMessage();
			if (bankLoan != null && StringUtil.isNotBlank(bankLoan.getLoanBank())) {
				if (!bankLoan.getLoanBank().equals("GACZ")&&!bankLoan.getLoanBank().equals("ZZB")) {
					message = e.getMessage().substring(27, message.length());
				}
			}
			return BaseRestResult.warn(message);

		}
		boolean isExpireTime = isExpireTime(billCode);
		if (!isExpireTime) {
			return BaseRestResult.warn("提示：“最大计划还款时间不等于到期日”").andRefresh();
		}
		// if (listExpire != null && listExpire.size() > 0) {
		// Collections.sort(listExpire);
		// for (int i = 0; i < listExpire.size(); i++) {
		// if (i == (listExpire.size() - 1)) {
		//
		// System.out.println(listExpire.get(listExpire.size() - 1).getOrder() +
		// "**********" + listExpire.get(listExpire.size() -
		// 1).getExpireTime());
		// }
		// }
		// }
		return BaseRestResult.buildClosestReloadResult();
	}

	/**
	 * 银行放款--动态表单 刷新
	 * 
	 * @param file
	 * @param billCode
	 * @param loanerName
	 * @param idNum
	 * @return
	 */
	@RequestMapping("/uploadExcel")
	public BaseRestResult<String> uploadExcel(@RequestParam(value = "file", required = false) MultipartFile file,
			String billCode, String loanerName, String idNum) {
		InputStream inputStream = null;
		O2oBankLoanData bankLoan = o2oLoanServiceImpl.queryBillCode(billCode);
		if (bankLoan==null) {
			return BaseRestResult.info("提示：“请先保存银行放款数据，再导入还款计划。”").andJquerySelectAjaxAction("#file_uploader");
		}
		String username = bankLoan.getLoanerName();

		try {
			inputStream = file.getInputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {

			if (bankLoan != null && StringUtil.isNotBlank(bankLoan.getLoanBank())) {
				if (bankLoan.getLoanBank().equals("JCB")) {

					ExcelApiHelper.readHandle(inputStream, new RowReadHandler() {

						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkJCBank(sheetIndex, rowIndex, rowDatas, 0, billCode, loanerName, idNum);
						}

					});
				} else if (bankLoan.getLoanBank().equals("GACZ")||bankLoan.getLoanBank().equals("ZZB")) {

					HSSFWorkbook workbook = null;
					try {
						workbook = new HSSFWorkbook(inputStream);
					} catch (FileNotFoundException e) {
						
						throw new RuntimeException("没有找到国安村镇还款计划的模板");
					} catch (IOException e) {
						throw new RuntimeException("没有找到国安村镇还款计划的模板");
					}

							checkGACZBank( workbook,username,0, billCode, loanerName, idNum);

				} else if (bankLoan.getLoanBank().equals("SPDBGZ")) {
					ExcelApiHelper.readHandle(inputStream, new RowReadHandler() {
						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkSPDBGZBank(sheetIndex, rowIndex, rowDatas, 0, billCode, loanerName, idNum);
						}
					});

				}else {

					ExcelApiHelper.readHandle(inputStream, new RowReadHandler() {
						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkQtherBank(sheetIndex, rowIndex, rowDatas, 0, billCode, loanerName, idNum);
						}
					});

				}

			}
		} catch (RuntimeException e) {
			String message = e.getMessage();
			if (bankLoan != null && StringUtil.isNotBlank(bankLoan.getLoanBank())) {
				if (!bankLoan.getLoanBank().equals("GACZ")&&!bankLoan.getLoanBank().equals("ZZB")) {
					message = e.getMessage().substring(27, message.length());
				}
			}
			log.info("导入Exception={}", message);			
			return BaseRestResult.warn(message);
		}

		InputStream inputStream1 = null;
		try {
			inputStream1 = file.getInputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			if (bankLoan != null && StringUtil.isNotBlank(bankLoan.getLoanBank())) {
				if (bankLoan.getLoanBank().equals("JCB")) {

					ExcelApiHelper.readHandle(inputStream1, new RowReadHandler() {

						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkJCBank(sheetIndex, rowIndex, rowDatas, 1, billCode, loanerName, idNum);
						}

					});
				} else if (bankLoan.getLoanBank().equals("GACZ")||bankLoan.getLoanBank().equals("ZZB")) {
					HSSFWorkbook workbook = null;
					try {
						workbook = new HSSFWorkbook(inputStream1);
					} catch (FileNotFoundException e) {
						
						throw new RuntimeException("没有找到国安村镇还款计划的模板");
					} catch (IOException e) {
						throw new RuntimeException("没有找到国安村镇还款计划的模板");
					}
					checkGACZBank(workbook, username,1, billCode, loanerName, idNum);

				} else if (bankLoan.getLoanBank().equals("SPDBGZ")) {
					ExcelApiHelper.readHandle(inputStream1, new RowReadHandler() {
						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkSPDBGZBank(sheetIndex, rowIndex, rowDatas, 1, billCode, loanerName, idNum);
						}
					});

				}else {

					ExcelApiHelper.readHandle(inputStream1, new RowReadHandler() {
						@Override
						public void handle(int sheetIndex, int rowIndex, Object[] rowDatas) {

							checkQtherBank(sheetIndex, rowIndex, rowDatas, 1, billCode, loanerName, idNum);
						}
					});

				}

			}
		} catch (RuntimeException e) {
			String message = e.getMessage();
			if (bankLoan != null && StringUtil.isNotBlank(bankLoan.getLoanBank())) {
				if (!bankLoan.getLoanBank().equals("GACZ")&&!bankLoan.getLoanBank().equals("ZZB")) {
					message = e.getMessage().substring(27, message.length());
				}
			}
			log.info("导入Exception={}", message);			
			return BaseRestResult.warn(message);

		}
		boolean isExpireTime = isExpireTime(billCode);
		if (!isExpireTime) {
			return BaseRestResult.info("提示：“最大计划还款时间不等于到期日”").andJquerySelectAjaxAction("#file_uploader");
		}
		// if (listExpire != null && listExpire.size() > 0) {
		// Collections.sort(listExpire);
		// for (int i = 0; i < listExpire.size(); i++) {
		// if (i == (listExpire.size() - 1)) {
		//
		// System.out.println(listExpire.get(listExpire.size() - 1).getOrder() +
		// "**********" + listExpire.get(listExpire.size() -
		// 1).getExpireTime());
		// }
		// }
		// }
		return BaseRestResult.buildClosestReloadResult();
	}


	/**
	 * @Title: isDouble
	 * @Description: 判断是否为double类型
	 * @author 张庆奇
	 * @param str
	 * @return
	 * @return boolean
	 * @throws @date
	 *             2016年6月20日 上午11:17:42
	 */
	private boolean isDouble(String str) {
		boolean flag = true;
		try {
			Double.parseDouble(str);
		} catch (Exception e) {
			flag = false;
		}
		return flag;

	}
	/**
	 * 变量listExpire
	 */
	// List<O2oListExpireTimeVo> listExpire = null;

	
	
	
	/**
	 * @Title: checkJCBank
	 * @Description: 导入江西银行的还款计划
	 * @author 张庆奇
	 * @param sheetIndex
	 * @param rowIndex
	 * @param rowDatas
	 * @param type
	 * @param billCode
	 * @param name
	 * @param idNumber
	 * @return void
	 * @throws
	 * @date 2017年8月24日 上午9:36:11
	 */
	public void checkJCBank(int sheetIndex, int rowIndex, Object[] rowDatas, int type, String billCode, String name, String idNumber){
	        String username="" ;//表格中填写的借款人姓名
		    String periods = "";//期数
			String paymentsDate = ""; //计划还款时间
			String residualPrincipal = "";//剩余本金
			String payPrincipal = "";//计划还款本金
			String payInterest = "";//计划还款利息
			
			if (sheetIndex == 0 && rowIndex == 0) {
				String flag = String.valueOf(rowDatas[0] == null ? "" : rowDatas[0]);
				if (!StringUtil.isEmpty(flag) && flag.equals("期号")) {
					throw new RuntimeException("模板不正确,请选择对应银行模板");
				}
			}
			if(sheetIndex == 0 && rowIndex ==1){
				username=String.valueOf(rowDatas[3] == null ? "" : rowDatas[3]);
				if (StringUtil.isEmpty(username)) {
					throw new RuntimeException("借款人姓名不能为空");
				}
				
				if(!username.equals(name)){
					
					throw new RuntimeException("没有找到"+name+"的还款计划");
				}
			}
				
			
			if (sheetIndex == 0 && rowIndex > 5) {
				
				if(rowIndex==12&&"合计:".equals(rowDatas[2])){
					return;
				}
				for (int i = 2; i < rowDatas.length; i++) {
					if (i == 2) {
						paymentsDate = String.valueOf(rowDatas[i] == null ? "" : rowDatas[i]);
						
					} else if (i == 3) {
						periods = String.valueOf(rowDatas[i] == null ? "" : rowDatas[i]);
					} else if (i == 5) {
						payPrincipal = String.valueOf(rowDatas[i] == null ? "" : rowDatas[i]);
					} else if (i == 6) {
						payInterest = String.valueOf(rowDatas[i] == null ? "" : rowDatas[i]);
					} else if (i == 7) {
						residualPrincipal = String.valueOf(rowDatas[i] == null ? "" : rowDatas[i]);
						
					}

				}
				String message = "第" + rowIndex + "行：";
				if (StringUtil.isEmpty(billCode) && StringUtil.isEmpty(name) && StringUtil.isEmpty(idNumber)) {

					throw new RuntimeException("贷款单号、借款人姓名、借款人证件号三者必须有一个");

				}
				// 如果单款单号不为空，则查看是否存在，如果存在，则取出借款姓名、证件号码
				
				//区分是老系统的单还是新系统的单
				if(!billCode.startsWith("O2O")){
					if (StringUtil.isNotBlank(billCode)) {
						O2oLoanapplyEntity loanapply = loanService.fingByBillCode(billCode);
						if (loanapply == null) {
							throw new RuntimeException("贷款单号不存在");

						} else {
							QueryCustomerVO vo = new QueryCustomerVO();
							vo.setCustomer_id(loanapply.getLoanId());
							/*
							 * CustomerBasicInfoEntity info =
							 * customerInfoService.getCustomerBasicInfoEntity(vo); if
							 * (info != null) { name = info.getCustomerName(); idNumber
							 * = info.getIdNumber(); } else { throw new
							 * RuntimeException("借款人信息不存在");
							 * 
							 * }
							 */

						}
					}

					// 如果单款单号为空并且贷款证件不为空，则根据借款人证件、借款状态为申报完成：SF、已放款/审批通过：O
					if (StringUtil.isNull(billCode) && StringUtil.isNotBlank(idNumber)) {
						QueryCustomerVO vo = new QueryCustomerVO();
						vo.setId_number(idNumber);
						CustomerBasicInfoEntity info = customerInfoService.getCustomerBasicInfoEntity(vo);
						if (info == null) {
							throw new RuntimeException("没有找到借款人证件号为：" + idNumber + "该用户的信息");
						} else {
							List<O2oLoanapplyEntity> list = o2oPaymentsService.findO2oLoanapplyEntity(info.getCustomerId());
							if (list != null) {
								if (list.size() == 0) {
									throw new RuntimeException("没有找到借款人证件号为" + idNumber + "该用户的申报完成或者放款完成的贷款申请信息");
								} else if (list.size() > 1) {
									throw new RuntimeException("[证件号为:" + idNumber + "的借款用户，需要手工录入相关信息，请在系统中查询贷款单号后，手动添加]");
								} else if (list.size() == 1) {

									billCode = list.get(0).getBillCode();
									name = info.getCustomerName();
								}

							}

						}

					}

					// 如果单款单号为空并且借款姓名不为空，身份证信息为空，则根据借款姓名、借款状态为申报完成：SF、已放款/审批通过：O查询billCode
					if (StringUtil.isNull(billCode) && StringUtil.isNull(idNumber) && StringUtil.isNotBlank(name)) {

						List<O2oLoanapplyEntity> list = o2oPaymentsService.findO2oLoanapplyEntityByName(name);
						if (list != null) {
							if (list.size() == 0) {
								throw new RuntimeException("没有找到姓名为" + name + "该用户的申报完成或者放款完成的贷款申请信息");
							} else if (list.size() > 1) {
								throw new RuntimeException("[姓名:" + name + "的借款用户，需要手工录入相关信息，请在系统中查询贷款单号后，手动添加]");
							} else if (list.size() == 1) {

								billCode = list.get(0).getBillCode();
								if (StringUtil.isNotBlank(list.get(0).getLoanId())) {
									QueryCustomerVO vo = new QueryCustomerVO();
									vo.setCustomer_id(list.get(0).getLoanId());
									CustomerBasicInfoEntity info = customerInfoService.getCustomerBasicInfoEntity(vo);
									idNumber = info.getIdNumber();
								}

							}
						}
					}
				}

				if (periods == null || "".equals(periods)) {

					throw new RuntimeException(message + "期数不能为空");

				}

				if (paymentsDate == null || "".equals(paymentsDate)) {

					throw new RuntimeException(message + "计划还款时间不能为空");

				}
				if (residualPrincipal == null || "".equals(residualPrincipal)) {

					throw new RuntimeException(message + "剩余本金不能为空");

				}
				if (payPrincipal == null || "".equals(payPrincipal)) {

					throw new RuntimeException(message + "计划还款本金不能为空");

				}

				if (payInterest == null || "".equals(payInterest)) {

					throw new RuntimeException(message + "计划还款利息不能为空");

				}
				if (!isDouble(periods)) {
					throw new RuntimeException(message + "期数格式不正确");
				}

				if (!isValidDate(paymentsDate)) {
					throw new RuntimeException(message + "计划还款时间格式不正确");
				}

				if (!isDouble(residualPrincipal)) {
					throw new RuntimeException(message + "剩余本金格式不正确");
				}
				if (!isDouble(payPrincipal)) {
					throw new RuntimeException(message + "计划还款本金格式不正确");
				}
				if (!isDouble(payInterest)) {
					throw new RuntimeException(message + "计划还款利息格式不正确");
				}
				long num = Math.round(Double.parseDouble(periods));
//				O2oLoanapplyEntity loanapply = loanService.fingByBillCode(billCode);
//				GregorianCalendar gc = new GregorianCalendar();
//				gc.setTime(loanapply.getPaymentTime());
//				gc.add(2, (int) num);
//				boolean contrast = contrastTime(gc.getTime(), new Date(DateUtils.formateStandDate(paymentsDate).getTime()));
//				if (!contrast) {
//					throw new RuntimeException("第" + num + "期，计划还款日期不等于该期到期日");
//				}
				// 数据插入
				if (type == 1) {
					O2oPaymentsPlan plan = new O2oPaymentsPlan();
					plan.setBillCode(billCode);
					plan.setName(name);
					plan.setIdNumber(idNumber);
					plan.setPeriods(num);
					plan.setPaymentsDate(new java.sql.Date(DateUtils.formateStandDate(paymentsDate).getTime()));
					plan.setResidualPrincipal(new BigDecimal(residualPrincipal));
					plan.setPayPrincipal(new BigDecimal(payPrincipal));
					plan.setPayInterest(new BigDecimal(payInterest));
					plan.setPayTotal(new BigDecimal(payPrincipal).add(new BigDecimal(payInterest)));
					plan.setDataType(2);
					plan.setRepaymentStatus("HKZ");
					String returnflag = o2oPaymentsService.savePayments(plan);
					if (returnflag == null) {
						throw new RuntimeException(message + "数据库保存异常");
					}
				}

			}
	    
	   }
	
	
	/**
	 * @Title: checkGACZBank
	 * @Description: 导入国安村镇银行的还款计划
	 * @author 张庆奇
	 * @param sheetIndex
	 * @param rowIndex
	 * @param rowDatas
	 * @param type
	 * @param billCode
	 * @param name
	 * @param idNumber
	 * @return void
	 * @throws
	 * @date 2017年8月24日 上午9:36:11
	 */

	public void checkGACZBank(HSSFWorkbook workbook, String username, int type, String billCode, String loanerName,
			String idNumber) {
		String periods = "";
		String paymentsDate = "";
		String residualPrincipal = "";
		String payPrincipal = "";
		String payInterest = "";
		HSSFSheet sheet = workbook.getSheet(username.trim());
		O2oBankLoanData bankLoan = o2oLoanServiceImpl.queryBillCode(billCode);
		if (sheet == null) {
			throw new RuntimeException("没有找到" + username + "的还款计划");
		}
		for (int rowIndex = 1; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {// 获取每行
			HSSFRow row = sheet.getRow(rowIndex);
			for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
				// 获取每个单元格

				if (i == 0) {
					periods = String.valueOf(row.getCell(i) == null ? "" : row.getCell(i));
				} else if (i == 1) {
					paymentsDate = String.valueOf(row.getCell(i) == null ? "" : parseExcel(row.getCell(i)));
				} else if (i == 2) {
					residualPrincipal = String.valueOf(row.getCell(i) == null ? "" : parseExcel(row.getCell(i)));
				} else if (i == 3) {
					payPrincipal = String.valueOf(row.getCell(i) == null ? "" : row.getCell(i));
				} else if (i == 4) {
					payInterest = String.valueOf(row.getCell(i) == null ? "" : row.getCell(i));
				}
			}

			String message = "第" + rowIndex + "行：";
			if (StringUtil.isEmpty(billCode) && StringUtil.isEmpty(loanerName) && StringUtil.isEmpty(idNumber)) {

				throw new RuntimeException("贷款单号、借款人姓名、借款人证件号三者必须有一个");

			}
			// 如果单款单号不为空，则查看是否存在，如果存在，则取出借款姓名、证件号码

			// 区分是老系统的单还是新系统的单
			if (!billCode.startsWith("O2O")) {
				if (StringUtil.isNotBlank(billCode)) {
					O2oLoanapplyEntity loanapply = loanService.fingByBillCode(billCode);
					if (loanapply == null) {
						throw new RuntimeException("贷款单号不存在");

					} else {
						QueryCustomerVO vo = new QueryCustomerVO();
						vo.setCustomer_id(loanapply.getLoanId());

					}
				}

				// 如果单款单号为空并且贷款证件不为空，则根据借款人证件、借款状态为申报完成：SF、已放款/审批通过：O
				if (StringUtil.isNull(billCode) && StringUtil.isNotBlank(idNumber)) {
					QueryCustomerVO vo = new QueryCustomerVO();
					vo.setId_number(idNumber);
					CustomerBasicInfoEntity info = customerInfoService.getCustomerBasicInfoEntity(vo);
					if (info == null) {
						throw new RuntimeException("没有找到借款人证件号为：" + idNumber + "该用户的信息");
					} else {
						List<O2oLoanapplyEntity> list = o2oPaymentsService.findO2oLoanapplyEntity(info.getCustomerId());
						if (list != null) {
							if (list.size() == 0) {
								throw new RuntimeException("没有找到借款人证件号为" + idNumber + "该用户的申报完成或者放款完成的贷款申请信息");
							} else if (list.size() > 1) {
								throw new RuntimeException("[证件号为:" + idNumber + "的借款用户，需要手工录入相关信息，请在系统中查询贷款单号后，手动添加]");
							} else if (list.size() == 1) {

								billCode = list.get(0).getBillCode();
								loanerName = info.getCustomerName();
							}

						}

					}

				}

				// 如果单款单号为空并且借款姓名不为空，身份证信息为空，则根据借款姓名、借款状态为申报完成：SF、已放款/审批通过：O查询billCode
				if (StringUtil.isNull(billCode) && StringUtil.isNull(idNumber) && StringUtil.isNotBlank(loanerName)) {

					List<O2oLoanapplyEntity> list = o2oPaymentsService.findO2oLoanapplyEntityByName(loanerName);
					if (list != null) {
						if (list.size() == 0) {
							throw new RuntimeException("没有找到姓名为" + loanerName + "该用户的申报完成或者放款完成的贷款申请信息");
						} else if (list.size() > 1) {
							throw new RuntimeException("[姓名:" + loanerName + "的借款用户，需要手工录入相关信息，请在系统中查询贷款单号后，手动添加]");
						} else if (list.size() == 1) {

							billCode = list.get(0).getBillCode();
							if (StringUtil.isNotBlank(list.get(0).getLoanId())) {
								QueryCustomerVO vo = new QueryCustomerVO();
								vo.setCustomer_id(list.get(0).getLoanId());
								CustomerBasicInfoEntity info = customerInfoService.getCustomerBasicInfoEntity(vo);
								idNumber = info.getIdNumber();
							}

						}
					}
				}
			}

			if (periods == null || "".equals(periods)) {

				throw new RuntimeException(message + "期数不能为空");

			}

			if (paymentsDate == null || "".equals(paymentsDate)) {

				throw new RuntimeException(message + "计划还款时间不能为空");

			}
			if (residualPrincipal == null || "".equals(residualPrincipal)) {

				throw new RuntimeException(message + "剩余本金不能为空");

			}
			if (payPrincipal == null || "".equals(payPrincipal)) {

				throw new RuntimeException(message + "计划还款本金不能为空");

			}

			if (payInterest == null || "".equals(payInterest)) {

				throw new RuntimeException(message + "计划还款利息不能为空");

			}
			if (!isDouble(periods)) {
				throw new RuntimeException(message + "期数格式不正确");
			}
			if (bankLoan.getLoanBank().equals("ZZB")) {
				if (!isValidDate1(paymentsDate)) {
					throw new RuntimeException(message + "计划还款时间格式不正确");
				}
			} else {
				if (!isValidDate(paymentsDate)) {
					throw new RuntimeException(message + "计划还款时间格式不正确");
				}
			}
			if (!isDouble(residualPrincipal)) {
				throw new RuntimeException(message + "剩余本金格式不正确");
			}
			if (!isDouble(payPrincipal)) {
				throw new RuntimeException(message + "计划还款本金格式不正确");
			}
			if (!isDouble(payInterest)) {
				throw new RuntimeException(message + "计划还款利息格式不正确");
			}
			long num = Math.round(Double.parseDouble(periods));
			// O2oLoanapplyEntity loanapply =
			// loanService.fingByBillCode(billCode);
			// GregorianCalendar gc = new GregorianCalendar();
			// gc.setTime(loanapply.getPaymentTime());
			// gc.add(2, (int) num);
			// boolean contrast = contrastTime(gc.getTime(), new
			// Date(DateUtils.formateStandDate(paymentsDate).getTime()));
			// if (!contrast) {
			// throw new RuntimeException("第" + num + "期，计划还款日期不等于该期到期日");
			// }
			// 数据插入
			if (type == 1) {
				O2oPaymentsPlan plan = new O2oPaymentsPlan();
				plan.setBillCode(billCode);
				plan.setName(loanerName);
				plan.setIdNumber(idNumber);
				plan.setPeriods(num);
				if(bankLoan.getLoanBank().equals("ZZB")){
					DateFormat formatter = new SimpleDateFormat("yyyyMMdd");
					
						try {
							Date date = formatter.parse(paymentsDate);
							plan.setPaymentsDate(new java.sql.Date(date.getTime()));
						 } catch (ParseException e) {
							
							e.printStackTrace();
						}
					
				}else{
				plan.setPaymentsDate(new java.sql.Date(DateUtils.formateStandDate(paymentsDate).getTime()));
				}
				plan.setResidualPrincipal(new BigDecimal(residualPrincipal));
				plan.setPayPrincipal(new BigDecimal(payPrincipal));
				plan.setPayInterest(new BigDecimal(payInterest));
				plan.setPayTotal(new BigDecimal(payPrincipal).add(new BigDecimal(payInterest)));
				plan.setDataType(2);
				plan.setRepaymentStatus("HKZ");
				String returnflag = o2oPaymentsService.savePayments(plan);
				if (returnflag == null) {
					throw new RuntimeException(message + "数据库保存异常");
				}
			}

		}

	}
	
	
	
	/**
	 * @Title: checkSPDBGZBank
	 * @Description: 浦发银行还款数据导入
	 * @author 张庆奇
	 * @param sheetIndex
	 * @param rowIndex
	 * @param rowDatas
	 * @param type
	 * @param billCode
	 * @param name
	 * @param idNumber
	 * @return void
	 * @throws
	 * @date 2017年11月23日 上午9:56:52
	 */
	public void checkSPDBGZBank(int sheetIndex, int rowIndex, Object[] rowDatas, int type, String billCode, String name, String idNumber){
		   String periods = "";
			String paymentsDate = "";
			String residualPrincipal = "";
			String payPrincipal = "";
			String payInterest = "";
			String loaner="";
		
			if(sheetIndex == 0 && rowIndex ==1){
				loaner=(String) rowDatas[1];
				
				if(StringUtils.isEmpty(loaner)){
					throw new  RuntimeException("借款人姓名不能为空");
				}
				loaner=loaner.trim();
				if(!name.equals(loaner)){
					throw new  RuntimeException("借款人姓名不正确");
				}
			}
			
			if (sheetIndex == 0 && rowIndex > 2) {
				
				for (int i = 0; i < rowDatas.length; i++) {
					if (i == 0) {
						periods = String.valueOf(rowDatas[0] == null ? "" : rowDatas[0]);
					} else if (i == 1) {
						paymentsDate = String.valueOf(rowDatas[1] == null ? "" : rowDatas[1]);
					} else if (i == 2) {
						residualPrincipal = String.valueOf(rowDatas[2] == null ? "" : rowDatas[2]);
					} else if (i == 3) {
						payPrincipal = String.valueOf(rowDatas[3] == null ? "" : rowDatas[3]);
					} else if (i == 4) {
						payInterest = String.valueOf(rowDatas[4] == null ? "" : rowDatas[4]);
					}

				}
				
				if(StringUtils.isNotEmpty(residualPrincipal)){
					residualPrincipal=residualPrincipal.trim();
				}
				
				if(StringUtils.isNotEmpty(payPrincipal)){
					payPrincipal=payPrincipal.trim();
				}
				
				if(StringUtils.isNotEmpty(payInterest)){
					payInterest=payInterest.trim();
				}
				
				String message = "第" + rowIndex + "行：";
				if (StringUtil.isEmpty(billCode) && StringUtil.isEmpty(name) && StringUtil.isEmpty(idNumber)) {

					throw new RuntimeException("贷款单号、借款人姓名、借款人证件号三者必须有一个");

				}
				// 如果单款单号不为空，则查看是否存在，如果存在，则取出借款姓名、证件号码
				
				//区分是老系统的单还是新系统的单
				if(!billCode.startsWith("O2O")){
					if (StringUtil.isNotBlank(billCode)) {
						O2oLoanapplyEntity loanapply = loanService.fingByBillCode(billCode);
						if (loanapply == null) {
							throw new RuntimeException("贷款单号不存在");

						} else {
							QueryCustomerVO vo = new QueryCustomerVO();
							vo.setCustomer_id(loanapply.getLoanId());
							/*
							 * CustomerBasicInfoEntity info =
							 * customerInfoService.getCustomerBasicInfoEntity(vo); if
							 * (info != null) { name = info.getCustomerName(); idNumber
							 * = info.getIdNumber(); } else { throw new
							 * RuntimeException("借款人信息不存在");
							 * 
							 * }
							 */

						}
					}

					// 如果单款单号为空并且贷款证件不为空，则根据借款人证件、借款状态为申报完成：SF、已放款/审批通过：O
					if (StringUtil.isNull(billCode) && StringUtil.isNotBlank(idNumber)) {
						QueryCustomerVO vo = new QueryCustomerVO();
						vo.setId_number(idNumber);
						CustomerBasicInfoEntity info = customerInfoService.getCustomerBasicInfoEntity(vo);
						if (info == null) {
							throw new RuntimeException("没有找到借款人证件号为：" + idNumber + "该用户的信息");
						} else {
							List<O2oLoanapplyEntity> list = o2oPaymentsService.findO2oLoanapplyEntity(info.getCustomerId());
							if (list != null) {
								if (list.size() == 0) {
									throw new RuntimeException("没有找到借款人证件号为" + idNumber + "该用户的申报完成或者放款完成的贷款申请信息");
								} else if (list.size() > 1) {
									throw new RuntimeException("[证件号为:" + idNumber + "的借款用户，需要手工录入相关信息，请在系统中查询贷款单号后，手动添加]");
								} else if (list.size() == 1) {

									billCode = list.get(0).getBillCode();
									name = info.getCustomerName();
								}

							}

						}

					}

					// 如果单款单号为空并且借款姓名不为空，身份证信息为空，则根据借款姓名、借款状态为申报完成：SF、已放款/审批通过：O查询billCode
					if (StringUtil.isNull(billCode) && StringUtil.isNull(idNumber) && StringUtil.isNotBlank(name)) {

						List<O2oLoanapplyEntity> list = o2oPaymentsService.findO2oLoanapplyEntityByName(name);
						if (list != null) {
							if (list.size() == 0) {
								throw new RuntimeException("没有找到姓名为" + name + "该用户的申报完成或者放款完成的贷款申请信息");
							} else if (list.size() > 1) {
								throw new RuntimeException("[姓名:" + name + "的借款用户，需要手工录入相关信息，请在系统中查询贷款单号后，手动添加]");
							} else if (list.size() == 1) {

								billCode = list.get(0).getBillCode();
								if (StringUtil.isNotBlank(list.get(0).getLoanId())) {
									QueryCustomerVO vo = new QueryCustomerVO();
									vo.setCustomer_id(list.get(0).getLoanId());
									CustomerBasicInfoEntity info = customerInfoService.getCustomerBasicInfoEntity(vo);
									idNumber = info.getIdNumber();
								}

							}
						}
					}
				}

				if (periods == null || "".equals(periods)) {

					throw new RuntimeException(message + "期数不能为空");

				}

				if (paymentsDate == null || "".equals(paymentsDate)) {

					throw new RuntimeException(message + "计划还款时间不能为空");

				}
				if (residualPrincipal == null || "".equals(residualPrincipal)) {

					throw new RuntimeException(message + "剩余本金不能为空");

				}
				if (payPrincipal == null || "".equals(payPrincipal)) {

					throw new RuntimeException(message + "计划还款本金不能为空");

				}

				if (payInterest == null || "".equals(payInterest)) {

					throw new RuntimeException(message + "计划还款利息不能为空");

				}
				if (!isDouble(periods)) {
					throw new RuntimeException(message + "期数格式不正确");
				}

				if (!isValidDate1(paymentsDate)) {
					throw new RuntimeException(message + "计划还款时间格式不正确");
				}

				if (!isDouble(residualPrincipal)) {
					throw new RuntimeException(message + "剩余本金格式不正确");
				}
				if (!isDouble(payPrincipal)) {
					throw new RuntimeException(message + "计划还款本金格式不正确");
				}
				if (!isDouble(payInterest)) {
					throw new RuntimeException(message + "计划还款利息格式不正确");
				}
				long num = Math.round(Double.parseDouble(periods))-1;
//				O2oLoanapplyEntity loanapply = loanService.fingByBillCode(billCode);
//				GregorianCalendar gc = new GregorianCalendar();
//				gc.setTime(loanapply.getPaymentTime());
//				gc.add(2, (int) num);
//				boolean contrast = contrastTime(gc.getTime(), new Date(DateUtils.formateStandDate(paymentsDate).getTime()));
//				if (!contrast) {
//					throw new RuntimeException("第" + num + "期，计划还款日期不等于该期到期日");
//				}
				// 数据插入
				if (type == 1) {
					O2oPaymentsPlan plan = new O2oPaymentsPlan();
					plan.setBillCode(billCode);
					plan.setName(name);
					plan.setIdNumber(idNumber);
					plan.setPeriods(num);
					DateFormat formatter = new SimpleDateFormat("yyyyMMdd");
					try {
						Date date = formatter.parse(paymentsDate);
						plan.setPaymentsDate(new java.sql.Date(date.getTime()));
					 } catch (ParseException e) {
						
						e.printStackTrace();
					}
							
					plan.setResidualPrincipal(new BigDecimal(residualPrincipal));
					plan.setPayPrincipal(new BigDecimal(payPrincipal));
					plan.setPayInterest(new BigDecimal(payInterest));
					plan.setPayTotal(new BigDecimal(payPrincipal).add(new BigDecimal(payInterest)));
					plan.setDataType(2);
					plan.setRepaymentStatus("HKZ");
					String returnflag = o2oPaymentsService.savePayments(plan);
					if (returnflag == null) {
						throw new RuntimeException(message + "数据库保存异常");
					}
				}

			}
	    
	   }
	
		
   /**
 * @Title: checkQtherBank
 * @Description: 导入还款计划其他银行
 * @author 张庆奇
 * @param sheetIndex
 * @param rowIndex
 * @param rowDatas
 * @param type
 * @param billCode
 * @param name
 * @param idNumber
 * @return void
 * @throws
 * @date 2017年8月24日 上午9:34:54
 */
public void checkQtherBank(int sheetIndex, int rowIndex, Object[] rowDatas, int type, String billCode, String name, String idNumber){
	   String periods = "";
		String paymentsDate = "";
		String residualPrincipal = "";
		String payPrincipal = "";
		String payInterest = "";
		if (sheetIndex == 0 && rowIndex > 0) {
			for (int i = 0; i < rowDatas.length; i++) {
				if (i == 0) {
					periods = String.valueOf(rowDatas[0] == null ? "" : rowDatas[0]);
				} else if (i == 1) {
					paymentsDate = String.valueOf(rowDatas[1] == null ? "" : rowDatas[1]);
				} else if (i == 2) {
					residualPrincipal = String.valueOf(rowDatas[2] == null ? "" : rowDatas[2]);
				} else if (i == 3) {
					payPrincipal = String.valueOf(rowDatas[3] == null ? "" : rowDatas[3]);
				} else if (i == 4) {
					payInterest = String.valueOf(rowDatas[4] == null ? "" : rowDatas[4]);
				}

			}
			String message = "第" + rowIndex + "行：";
			if (StringUtil.isEmpty(billCode) && StringUtil.isEmpty(name) && StringUtil.isEmpty(idNumber)) {

				throw new RuntimeException("贷款单号、借款人姓名、借款人证件号三者必须有一个");

			}
			// 如果单款单号不为空，则查看是否存在，如果存在，则取出借款姓名、证件号码
			
			//区分是老系统的单还是新系统的单
			if(!billCode.startsWith("O2O")){
				if (StringUtil.isNotBlank(billCode)) {
					O2oLoanapplyEntity loanapply = loanService.fingByBillCode(billCode);
					if (loanapply == null) {
						throw new RuntimeException("贷款单号不存在");

					} else {
						QueryCustomerVO vo = new QueryCustomerVO();
						vo.setCustomer_id(loanapply.getLoanId());
						/*
						 * CustomerBasicInfoEntity info =
						 * customerInfoService.getCustomerBasicInfoEntity(vo); if
						 * (info != null) { name = info.getCustomerName(); idNumber
						 * = info.getIdNumber(); } else { throw new
						 * RuntimeException("借款人信息不存在");
						 * 
						 * }
						 */

					}
				}

				// 如果单款单号为空并且贷款证件不为空，则根据借款人证件、借款状态为申报完成：SF、已放款/审批通过：O
				if (StringUtil.isNull(billCode) && StringUtil.isNotBlank(idNumber)) {
					QueryCustomerVO vo = new QueryCustomerVO();
					vo.setId_number(idNumber);
					CustomerBasicInfoEntity info = customerInfoService.getCustomerBasicInfoEntity(vo);
					if (info == null) {
						throw new RuntimeException("没有找到借款人证件号为：" + idNumber + "该用户的信息");
					} else {
						List<O2oLoanapplyEntity> list = o2oPaymentsService.findO2oLoanapplyEntity(info.getCustomerId());
						if (list != null) {
							if (list.size() == 0) {
								throw new RuntimeException("没有找到借款人证件号为" + idNumber + "该用户的申报完成或者放款完成的贷款申请信息");
							} else if (list.size() > 1) {
								throw new RuntimeException("[证件号为:" + idNumber + "的借款用户，需要手工录入相关信息，请在系统中查询贷款单号后，手动添加]");
							} else if (list.size() == 1) {

								billCode = list.get(0).getBillCode();
								name = info.getCustomerName();
							}

						}

					}

				}

				// 如果单款单号为空并且借款姓名不为空，身份证信息为空，则根据借款姓名、借款状态为申报完成：SF、已放款/审批通过：O查询billCode
				if (StringUtil.isNull(billCode) && StringUtil.isNull(idNumber) && StringUtil.isNotBlank(name)) {

					List<O2oLoanapplyEntity> list = o2oPaymentsService.findO2oLoanapplyEntityByName(name);
					if (list != null) {
						if (list.size() == 0) {
							throw new RuntimeException("没有找到姓名为" + name + "该用户的申报完成或者放款完成的贷款申请信息");
						} else if (list.size() > 1) {
							throw new RuntimeException("[姓名:" + name + "的借款用户，需要手工录入相关信息，请在系统中查询贷款单号后，手动添加]");
						} else if (list.size() == 1) {

							billCode = list.get(0).getBillCode();
							if (StringUtil.isNotBlank(list.get(0).getLoanId())) {
								QueryCustomerVO vo = new QueryCustomerVO();
								vo.setCustomer_id(list.get(0).getLoanId());
								CustomerBasicInfoEntity info = customerInfoService.getCustomerBasicInfoEntity(vo);
								idNumber = info.getIdNumber();
							}

						}
					}
				}
			}

			if (periods == null || "".equals(periods)) {

				throw new RuntimeException(message + "期数不能为空");

			}

			if (paymentsDate == null || "".equals(paymentsDate)) {

				throw new RuntimeException(message + "计划还款时间不能为空");

			}
			if (residualPrincipal == null || "".equals(residualPrincipal)) {

				throw new RuntimeException(message + "剩余本金不能为空");

			}
			if (payPrincipal == null || "".equals(payPrincipal)) {

				throw new RuntimeException(message + "计划还款本金不能为空");

			}

			if (payInterest == null || "".equals(payInterest)) {

				throw new RuntimeException(message + "计划还款利息不能为空");

			}
			if (!isDouble(periods)) {
				throw new RuntimeException(message + "期数格式不正确");
			}

			if (!isValidDate(paymentsDate)) {
				throw new RuntimeException(message + "计划还款时间格式不正确");
			}

			if (!isDouble(residualPrincipal)) {
				throw new RuntimeException(message + "剩余本金格式不正确");
			}
			if (!isDouble(payPrincipal)) {
				throw new RuntimeException(message + "计划还款本金格式不正确");
			}
			if (!isDouble(payInterest)) {
				throw new RuntimeException(message + "计划还款利息格式不正确");
			}
			long num = Math.round(Double.parseDouble(periods));
//			O2oLoanapplyEntity loanapply = loanService.fingByBillCode(billCode);
//			GregorianCalendar gc = new GregorianCalendar();
//			gc.setTime(loanapply.getPaymentTime());
//			gc.add(2, (int) num);
//			boolean contrast = contrastTime(gc.getTime(), new Date(DateUtils.formateStandDate(paymentsDate).getTime()));
//			if (!contrast) {
//				throw new RuntimeException("第" + num + "期，计划还款日期不等于该期到期日");
//			}
			// 数据插入
			if (type == 1) {
				O2oPaymentsPlan plan = new O2oPaymentsPlan();
				plan.setBillCode(billCode);
				plan.setName(name);
				plan.setIdNumber(idNumber);
				plan.setPeriods(num);
				plan.setPaymentsDate(new java.sql.Date(DateUtils.formateStandDate(paymentsDate).getTime()));
				plan.setResidualPrincipal(new BigDecimal(residualPrincipal));
				plan.setPayPrincipal(new BigDecimal(payPrincipal));
				plan.setPayInterest(new BigDecimal(payInterest));
				plan.setPayTotal(new BigDecimal(payPrincipal).add(new BigDecimal(payInterest)));
				plan.setDataType(2);
				plan.setRepaymentStatus("HKZ");
				String returnflag = o2oPaymentsService.savePayments(plan);
				if (returnflag == null) {
					throw new RuntimeException(message + "数据库保存异常");
				}
			}

		}
    
   }

	/**
	 * @Description:
	 * 				<p>
	 *               查询实际放款时间是否等于计划放款最后时间
	 *               </p>
	 * @author CXW
	 * @date 2016年9月18日 上午9:42:50
	 * @version V1.0
	 * @param billCode
	 * @return boolean
	 */
	public boolean isExpireTime(String billCode) {
		O2oLoanapplyEntity loan = loanService.fingByBillCode(billCode);
		O2oPaymentsPlan payment = paymentServiceImpl.findByBillCode(billCode);
		if (null == loan || null == payment) {
			return false;
		} else {
			if (null == loan.getEndBankDate() || null == payment.getPaymentsDate()) {
				return false;
			}
			Calendar loanDate = Calendar.getInstance();
			loanDate.setTime(loan.getEndBankDate());
			Calendar paymentDate = Calendar.getInstance();
			paymentDate.setTime(payment.getPaymentsDate());
			boolean isSameYear = loanDate.get(Calendar.YEAR) == paymentDate.get(Calendar.YEAR);
			boolean isSameMonth = isSameYear && loanDate.get(Calendar.MONTH) == paymentDate.get(Calendar.MONTH);
			boolean isSameDate = isSameMonth && loanDate.get(Calendar.DAY_OF_MONTH) == paymentDate.get(Calendar.DAY_OF_MONTH);
			return isSameDate;
		}
	}
	
	
	@RequestMapping("/index")
	public PaginationRestResult findPageO2OData(O2oLoanForm o2oLoanForm) {
		o2oLoanForm.setPageSize(10);
		
		
		List<User> users=sysOrgService.checkDataPermission(UserHolder.getUser().getNo());
		String no="";
		if (users!=null) {
			for (User user : users) {
				no+=user.getNo()+",";
			}
		}
		if (!"".equals(no)) {
			if (",".equals(no.substring(no.length()-1, no.length()))) {
				no=no.substring(0, no.length()-1);
			}
		}
		log.info("userno={}", no);
		o2oLoanForm.setXdjlNo(no);
		
		PageResult<Object[]> pageResult = o2oLoanService.loanList(o2oLoanForm);
		String[] titles = { "贷款单号", "姓名", "借款证件号码", "贷款类型", "申请金额(万元)", "银行", "放款金额(万元)", "利率", "期限/(月)", "实际放款日" };
		PaginationInfo paginationInfo = new PaginationInfo(pageResult, titles, UrlUtil.resetParam(RequestContext.getRequestUrlWithQueryString(), "showPage", ""));
		paginationInfo.setBuildPagingNavigation(true);
		paginationInfo.setFirstRenderColumnIndex(0);
		Button findBtn = ButtonHelper.createAjaxButton("", "/bank/show_loan?type=show&bankLoanId=");
		//Button updabon = ButtonHelper.createRedirectButton("修改放款和计划", "/bank/edit_loan?type=edit&bankLoanId=", "bankEdit");
		Button[] titleButtons = new Button[] { findBtn };
		//Button[] dealButton = new Button[] {  findbon, updabon };
		SelectDataOption[] selectDataOptions = { null, null, null, new SelectDataOption("OTO_LOAN_TYPE"), null, null, null, null, null, null,new SelectDataOption("OTO_PAY_TYPE") };
		paginationInfo.setTitleButtons(titleButtons);
		//paginationInfo.setMoreButtons(dealButton);
		paginationInfo.setFirstRenderColumnIndex(1);
		paginationInfo.setSelectDataOptions(selectDataOptions);
		paginationInfo.setFormats(new Format[] { null, null, null, null, new DecimalFormat(",##0.00"), null, new DecimalFormat(",##0.00"), null, null, null });
		paginationInfo.setRendTo("#o2o_result");
		return PaginationRestResult.buildPaginationRestResult(paginationInfo);
	}

	@RequestMapping("/oldLoan_list")
	public PaginationRestResult findPageOaData(O2oLoanForm o2oLoanForm) {
		o2oLoanForm.setPageSize(10);
		PageResult<Object[]> pageResult = o2oLoanService.loanOldList(o2oLoanForm);
		String[] titles = { "贷款单号", "姓名", "借款证件号码", "贷款类型", "申请金额", "银行", "放款金额", "利率", "期限/(月)", "实际放款日" };
		PaginationInfo paginationInfo = new PaginationInfo(pageResult, titles, UrlUtil.resetParam(RequestContext.getRequestUrlWithQueryString(), "oldShowPage", ""));
		paginationInfo.setBuildPagingNavigation(true);
		paginationInfo.setFirstRenderColumnIndex(0);
		Button findbon = ButtonHelper.createRedirectButton("查看放款和计划", "/bank/show_loan?type=show&bankLoanId=", "bankShow");
		Button updabon = ButtonHelper.createRedirectButton("修改放款和计划", "/bank/edit_loan?type=edit&bankLoanId=", "bankEdit");
		Button[] titleButtons = new Button[] { ButtonHelper.createOpenButton(null , "/afterloan/detail?billCode={1}") };
		Button[] dealButton = new Button[] {  findbon, updabon };
		SelectDataOption[] selectDataOptions = { null, null, null, new SelectDataOption("OTO_LOAN_TYPE"), null, null, null, null, null, null,new SelectDataOption("OTO_PAY_TYPE") };
		paginationInfo.setTitleButtons(titleButtons);
		paginationInfo.setMoreButtons(dealButton);
		paginationInfo.setSelectDataOptions(selectDataOptions);
		paginationInfo.setFormats(new Format[] { null, null, null, null, new DecimalFormat(",##0.00"), null, new DecimalFormat(",##0.00"), null, null, null });
		paginationInfo.setRendTo("#oa_result");
		return PaginationRestResult.buildPaginationRestResult(paginationInfo);
	}
	
	@RequestMapping("/show_loan")
	public BaseRestResult<String> showLoan(O2oLoanForm o2oLoanForm) {
		String bankLoanId = o2oLoanForm.getBankLoanId();
		O2oBankLoanData  o2oBankLoanData = o2oLoanServiceImpl.findEntity(bankLoanId); //先查出银行放款数据
		o2oLoanForm.setPayType(o2oBankLoanData.getPayType());
		o2oLoanForm.setBillCode(o2oBankLoanData.getBillCode());
		String url= common(o2oLoanForm, "show");
		
		return HtmlRestResult.popHtmlFragment(url);
	}
	
	
	public String common(O2oLoanForm o2oLoanForm, String operType) {
		String returnMsg = "";
		boolean isExpireTime = isExpireTime(o2oLoanForm.getBillCode());
		String oldPaytype="";
		if(!isExpireTime){
			ViewContext.put("dateNotendDate", "提示：“最大计划还款时间不等于到期日”");
		}
		O2oLoanapplyEntity loanEntity = loanService.fingByBillCode(o2oLoanForm.getBillCode());
		ViewContext.put("loanEntity", loanEntity);
		if (StringUtils.isNotEmpty(o2oLoanForm.getBillCode())) {
			List<Object[]> bill_list = null;
			if (operType.equals("edit")) {
//				if(o2oLoanForm.getBillCode().startsWith("O2O")){ //老系统数据以O2O开头
//					bill_list = o2oLoanService.getO2OBankData(o2oLoanForm.getBillCode()); 
//				}else{
					bill_list = o2oLoanService.getBankData(o2oLoanForm.getBillCode(),o2oLoanForm.getPayType());
//				}
				returnMsg = "/bank/add_loan";
			} else if (operType.equals("show")) {
//				if(o2oLoanForm.getBillCode().startsWith("O2O")){ //老系统数据以O2O开头
//					bill_list = o2oLoanService.getO2OBankData(o2oLoanForm.getBillCode()); 
//				}else{
					bill_list = o2oLoanService.getBankData(o2oLoanForm.getBillCode(),o2oLoanForm.getPayType());
//				}
				returnMsg = "/bank/show_loan";
			} else {
				bill_list = o2oLoanService.isBillCodeExists(o2oLoanForm.getBillCode(), null, null);
				returnMsg = "/bank/add_loan";
			}
			
			if ((bill_list != null && bill_list.size() > 0)) {
				Object[] o = bill_list.get(0);
				if (o[1] != null) {
					String loanerCard = (String) o[1];
					o2oLoanForm.setLoanerCard(loanerCard);
				}
				if (o[2] != null) {
					String loanerName = (String) o[2];
					o2oLoanForm.setLoanerName(loanerName);
				}
				if (o[3] != null) {
					String loanBank = (String) o[3];
					o2oLoanForm.setLoanBank(loanBank);
				}
				if (o[4] != null) {
					BigDecimal applyAmount = (BigDecimal) o[4];
					o2oLoanForm.setApplyAmount(applyAmount);
				}
				if (o[5] != null) {
					BigDecimal loanRate = (BigDecimal) o[5];
					o2oLoanForm.setLoanRate(loanRate);
				}

				if (o[6] != null) {
					BigDecimal loanAmount = (BigDecimal) o[6];
					o2oLoanForm.setLoanAmount(loanAmount);
				}
				if (o[7] != null) {
					int loanTerm = 0;
					if(o[7] instanceof  String){
						 loanTerm = Integer.parseInt((String)o[7]);
					}else{
						 loanTerm = (int) o[7];
					}
					o2oLoanForm.setLoanTerm(loanTerm);
				}
				if (o[8] != null) {
					String loanDate = "";
					if (operType.equals("edit") || operType.equals("show")) {
						if(o[8] instanceof Date){
							Date loan = (Date) o[8];
							loanDate = DateUtils.formateStandDate(loan);
						}else{
							loanDate = (String) o[8];
						}
					} else {
						loanDate = (String) o[8];
					}
					o2oLoanForm.setLoanDate(loanDate);
				}
				if (o[9] != null) {
//					if(o2oLoanForm.getBillCode().startsWith("O2O")){ //老系统数据以O2O开头
//						O2oLoanapplyEntity loan = new O2oLoanapplyEntity();
//						java.sql.Date endBankDate = (java.sql.Date) o[9];
//						loan.setEndBankDate(endBankDate);
//						ViewContext.put("loanEntity", loan);
//					}else{
						String loanBankCode = (String) o[9];
						o2oLoanForm.setLoanBankCode(loanBankCode);
//					}
				}
//				if(o2oLoanForm.getBillCode().startsWith("O2O")){ //老系统数据以O2O开头
//					if (o[10] != null) {
//						oldPaytype = (String) o[10];
//					}
//				}
			}
		}
		ViewContext.put("voo", o2oLoanForm);
		ViewContext.put("operType", operType);
		
		// 银行还款计划列表
		payMents(o2oLoanForm.getBillCode(), o2oLoanForm.getType(),o2oLoanForm.getPayType());
		
		if(o2oLoanForm.getBillCode().startsWith("O2O")){ //老系统数据以O2O开头
			ViewContext.put("payType",oldPaytype );
		}
		// 还款记录查询
		if (operType.equals("show"))
			hkHistory(o2oLoanForm.getBillCode());
		return returnMsg;
	}

	public void payMents(String billCode, String type,String payType) {
		O2oPaymentsForm o2oPaymentsForm = new O2oPaymentsForm();
		o2oPaymentsForm.setBillCode(billCode);
		o2oPaymentsForm.setPageSize(100);
		o2oPaymentsForm.setPayType(payType);
		PageResult<Object[]> pageResult = o2oPaymentsService.paymentsList(o2oPaymentsForm);
		String[] titles = { "贷款单号", "借款人姓名", "借款人证件号", "还款期数", "应还款日期", "剩余本金", "应还本金", "应还利息", "未还本金", "未还利息", "罚息", "状态" };
		String pageUrl = "/payments/payments_list";
		PaginationInfo paginationInfo = new PaginationInfo(pageResult, titles, pageUrl + "&showPage=");
		paginationInfo.setBuildPagingNavigation(false);
		paginationInfo.setFirstRenderColumnIndex(1);
		if (type.equals("edit")) {
			AjaxConfirmButton[] dealButton = new AjaxConfirmButton[] { new AjaxConfirmButton("删除", "/paymentsRest/delPayments?bill_code=" + billCode + "&payments_id=") };
			paginationInfo.setMoreButtons(dealButton);
		} else if (type.equals("show")) {
			if (StringUtil.isNotBlank(payType)) {
				AjaxButton[] hkButton = new AjaxButton[] { new AjaxButton("发起还款", "/paymentsRest/repayment?billCode=" + billCode + "&paymentsId=") };
				paginationInfo.setMoreButtons(hkButton);
			}
		}
		SelectDataOption[] selectDataOptions = { null, null, null, null, null, null, null, null, null, null, null, new SelectDataOption("REPAYMENT_STATUS") };
		paginationInfo.setSelectDataOptions(selectDataOptions);
		paginationInfo.setFormats(new Format[] { null, null, null, null, null, new DecimalFormat(",##0.00"), new DecimalFormat(",##0.00"), new DecimalFormat(",##0.00"), new DecimalFormat(",##0.00"), new DecimalFormat(",##0.00"), new DecimalFormat(",##0.00"), null });
		ViewContext.put("paginationInfo", paginationInfo);
		ViewContext.put("vo", o2oPaymentsForm);
		//ViewContext.put("payType", getLoantype(billCode));
		ViewContext.put("payType", payType);
		if (pageResult.getData().size() > 0) {
			ViewContext.put("upload", "upload");
		}

	}

	/**
	 * @Description: <p> 查询还款记录 </p>
	 * @author CXW
	 * @date 2016年9月8日 下午1:36:37
	 * @version V1.0
	 * @param billCode
	 * @return void
	 */
	public void hkHistory(String billCode) {
		O2oPaymentsForm o2oPaymentsForm = new O2oPaymentsForm();
		o2oPaymentsForm.setBillCode(billCode);
		o2oPaymentsForm.setPageSize(10);
		PageResult<Object[]> pageResult = o2oPaymentsService.repaymentsList(o2oPaymentsForm);
		String titles[] = { "对应期还款数", "应还日期", "实际还款日期", "还款本金", "还款利息", "罚息", "还款凭证", "流程状态" };// 
		AjaxButton start_upButton = new AjaxButton("发起流程", "/paymentsRest/start?id=");
		AjaxButton detailButton = new AjaxButton("详情", "/paymentsRest/recordView?billCode=" + billCode + "&referId=");
		
		AjaxButton editButton = new AjaxButton("编辑", "/paymentsRest/findRecord?recordId=");
		AjaxConfirmButton deleteButton = new AjaxConfirmButton("删除", "/paymentsRest/deleteRecord?recordId=");
		
		deleteButton.setShowButtonFilter(showBottun);
		editButton.setShowButtonFilter(showBottun);
		
		Button[] dealButton = new Button[] { start_upButton, detailButton, editButton, deleteButton };
		PaginationInfo o2oPagingInfo = new PaginationInfo(pageResult, titles);
		o2oPagingInfo.setTitleButtons(new Button[]{null,null,null,null,null,null,ButtonHelper.createOpenButton(null, "/file/download?datalistno={9}")});
		o2oPagingInfo.setMoreButtons(dealButton);
		o2oPagingInfo.setBuildPagingNavigation(false);
		o2oPagingInfo.setFirstRenderColumnIndex(1);
		o2oPagingInfo.setBuildRowNumber(true);
		SelectDataOption[] selectDataOptions = { null, null, null, null, null, null, null, new SelectDataOption("PAY_STATUS") };
		o2oPagingInfo.setSelectDataOptions(selectDataOptions);
		ViewContext.put("o2oPagingInfo", o2oPagingInfo);
	}
	

	/**
	 * 是否显示按钮过滤器
	 */
	public ShowButtonFilter showBottun = new ShowButtonFilter() {
		
		@Override
		public boolean filter(Object param) {
			Object[] params = (Object[]) param;
			if (params[7].equals(StatusContexts.O2O_NEW)) {
				return true;
			} else {
				return false;
			}
		}
	};
	
	
    private String parseExcel(Cell cell) {  
        String result = new String();  
        switch (cell.getCellType()) {  
        case HSSFCell.CELL_TYPE_NUMERIC: {  
            short format = cell.getCellStyle().getDataFormat();  
            if(format == 14 || format == 31 || format == 57 || format == 58){   //excel中的时间格式  
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");    
                double value = cell.getNumericCellValue();    
                Date date = DateUtil.getJavaDate(value);    
                result = sdf.format(date);    
            }  
            // 判断当前的cell是否为Date  
            else if (HSSFDateUtil.isCellDateFormatted(cell)) {  //先注释日期类型的转换，在实际测试中发现HSSFDateUtil.isCellDateFormatted(cell)只识别2014/02/02这种格式。  
                // 如果是Date类型则，取得该Cell的Date值           // 对2014-02-02格式识别不出是日期格式  
                Date date = cell.getDateCellValue();  
                DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");  
                result= formater.format(date);  
            } else { // 如果是纯数字  
                // 取得当前Cell的数值  
            	result = NumberToTextConverter.toText(cell.getNumericCellValue());   
                  
            }  
            break;  
        }  
        case HSSFCell.CELL_TYPE_STRING:// String类型  
            result = cell.getRichStringCellValue().toString();  
            break;  
            
        case Cell.CELL_TYPE_FORMULA:  
            // cell.getCellFormula();  
            try {  
            /* 
             * 此处判断使用公式生成的字符串有问题，因为HSSFDateUtil.isCellDateFormatted(cell)判断过程中cell 
             * .getNumericCellValue();方法会抛出java.lang.NumberFormatException异常 
             */  
             if (HSSFDateUtil.isCellDateFormatted(cell)) {  
                Date date = cell.getDateCellValue();  
                result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1) +"-" + date.getDate();  
                break;  
             } else {  
            	 result = String.valueOf(cell.getNumericCellValue());  
             }  
             } catch (IllegalStateException e) {  
            	 result = String.valueOf(cell.getRichStringCellValue());  
             }  
            break;    
        case HSSFCell.CELL_TYPE_BLANK:  
            result = "";  
        default:  
            result = "";  
            break;  
        }  
        return result;  
    }  
}
